.import QtQuick.LocalStorage 2.0 as Sql

function getDatabase() {
    return Sql.LocalStorage.openDatabaseSync("ShoeWashingMachine", "1.0", "StorageDatabase", 100000);
}

function initialize() {
    var db = getDatabase();
    db.transaction(
        function(tx) {
            tx.executeSql('CREATE TABLE IF NOT EXISTS SWM_Task(ID INTEGER PRIMARY KEY AUTOINCREMENT,\
                            TaskID INT,\
                            Sequence INT,\
                            Status varchar(20),\
                            Model INT,\
                            Time INT,\
                            StartTime DATETIME,\
                            EndTime DATETIME,\
                            DetergentType varchar(20),\
                            DetergentDose varchar(20),\
                            Temperature Int)');
            tx.executeSql('CREATE TABLE IF NOT EXISTS SWM_TaskStatus(TaskID INTEGER PRIMARY KEY AUTOINCREMENT,\
                            TaskStatus varchar(20),\
                            StartTime DATETIME,\
                            EndTime DATETIME)');
            tx.executeSql('CREATE TABLE IF NOT EXISTS SWM_TaskModel(ModelID INT primary key,\
                            ModelName varchar(20))');
            tx.executeSql('CREATE TABLE IF NOT EXISTS SWM_Model(ID INTEGER PRIMARY KEY AUTOINCREMENT,\
                            ModelID INT,\
                            Sequence INT,\
                            Model INT,\
                            Time INT,\
                            DetergentType varchar(20),\
                            DetergentDose varchar(20),\
                            Temperature Int)');
            tx.executeSql('CREATE TABLE IF NOT EXISTS SWM_ModelName(ModelID INTEGER PRIMARY KEY AUTOINCREMENT,\
                            ModelName varchar(20))');
        }
    );
}

//创建一个mode
function createModeName(name) {
    var db = getDatabase();
    var id = -1;
    db.transaction(
        function(tx) {
            var cs = tx.executeSql('SELECT * FROM SWM_ModelName WHERE ModelName=?;',[name]);
            if(cs.rows.length > 0) {
                return -1;
            }
            var rs = tx.executeSql('INSERT OR REPLACE INTO SWM_ModelName(ModelName) VALUES (?);', [name]);

            var fx = tx.executeSql('SELECT * FROM SWM_ModelName WHERE ModelName=?;',[name]);
            if (fx.rows.length > 0) {
                id = fx.rows.item(0).ModelID;
            } else {
                id = -2;
            }
        }
    );
    return id;
}

//根据id、流程、流程模式、时间、清洁剂、温度创建一个流程模式
function createMode(id,suq,taskMode,time,type,dose,temp) {
    var db = getDatabase();
    var res = "";
    db.transaction(
        function(tx) {
            var rs = tx.executeSql('INSERT OR REPLACE INTO SWM_Model(ModelID,Sequence,Model,Time,DetergentType,DetergentDose,Temperature) VALUES (?,?,?,?,?,?,?);',[id,suq,taskMode,time,type,dose,temp]);
            if (rs.rowsAffected > 0) {
                res = "OK";
            } else {
                res = "Error";
            }
        }
    );
}

//根据流程模式名称获取流程模式id
function getTaskModeID(name) {
    var db = getDatabase();
    var id = "";
    db.transaction(
        function(tx) {
            var rs = tx.executeSql('SELECT * FROM SWM_TaskModel WHERE ModelName=?;',[name]);
            if (rs.rows.length > 0) {
                id = rs.rows.item(0).ModelID;
            } else {
                id = -2;
            }
        }
    );
    return id;
}

//根据流程模式的id获取流程模式名称
function getTaskModeName(id) {
    var db = getDatabase();
    var name = "";
    db.transaction(
        function(tx) {
            var rs = tx.executeSql('SELECT * FROM SWM_TaskModel WHERE ModelID=?;',[id]);
            if (rs.rows.length > 0) {
                name = rs.rows.item(0).ModelName;
            }
        }
    );
    return name;
}

//获取所有的模式名称
function getModelFromModelName() {
    var db = getDatabase();
    var rows;
    db.transaction(
        function(tx) {
            var rs = tx.executeSql('SELECT * FROM SWM_ModelName;');
            if (rs.rows.length > 0) {
                rows = rs.rows
            }
        }
    );
    return rows;
}

//根据ModelID从SWM_Model表中返回模式给Model
function getModelFromModel(id) {
    var db = getDatabase();
    var rows;
    db.transaction(
        function(tx) {
            var rs = tx.executeSql('SELECT * FROM SWM_Model WHERE ModelID=?;',[id]);
            if (rs.rows.length > 0) {
                rows = rs.rows
            }
        }
    );
    return rows;
}

//更新任务状态
function updateTaskStatus(taskid,status) {
    var db = getDatabase();
    db.transaction(
        function(tx) {
            var rs = tx.executeSql('UPDATE SWM_TaskStatus SET TaskStatus=? WHERE TaskID=?;',[status,taskid]);
        }
    );
}

//创建任务，返回任务ID
function createTask() {
    var db = getDatabase();
    var id = -1;
    db.transaction(
        function(tx) {
            var cs = tx.executeSql('SELECT * FROM SWM_TaskStatus WHERE TaskStatus=?;',['正在执行']);
            if(cs.rows.length > 0) {
                var ingid = cs.rows.item(0).TaskID;
                updateTaskStatus(ingid,'取消执行');
            }
            var today=new Date()
            var mon = today.getMonth()+1;
            var nowTime = today.getFullYear()+"-"+mon+"-"+today.getDate()+" "+today.getHours()+":"+today.getMinutes()+":"+today.getSeconds();

            var rs = tx.executeSql('INSERT OR REPLACE INTO SWM_TaskStatus(TaskStatus,StartTime) VALUES (?,?);', ['正在执行',nowTime]);

            var fx = tx.executeSql('SELECT * FROM SWM_TaskStatus WHERE TaskStatus=?;',['正在执行']);
            if (fx.rows.length > 0) {
                id = fx.rows.item(0).TaskID;
            } else {
                id = -2;
            }
        }
    );
    return id;
}

//根据模式ID和任务ID，创建任务流程SWM_Task
function createTaskProcessFromMode(id,taskid) {
    var db = getDatabase();
    var process = new Array;
    db.transaction(
        function(tx) {
            var cs = tx.executeSql('SELECT * FROM SWM_Model WHERE ModelID=?;',[id]);
            if(cs.rows.length > 0) {
                var length = cs.rows.length;
                for(var i=0;i<length;i++) {
                    process[i] = {Model:cs.rows.item(i).Model, Time:cs.rows.item(i).Time, DetergentType:cs.rows.item(i).DetergentType, DetergentDose:cs.rows.item(i).DetergentDose, Temperature:cs.rows.item(i).Temperature};
                }
            }
            for(var j=0;j<process.length;j++) {
                var status = "";
                if(j==0) {
                    status = "正在执行";
                } else {
                    status = "未执行";
                }
                var as = tx.executeSql('INSERT OR REPLACE INTO SWM_Task(TaskID,Sequence,Status,Model,Time,DetergentType,DetergentDose,Temperature) VALUES (?,?,?,?,?,?,?,?);',[taskid, j+1,status,process[j].Model,process[j].Time,process[j].DetergentType,process[j].DetergentDose,process[j].Temperature]);
            }
        }
    );
}

//更新所有执行中的任务为中断执行
function updateStatusForOthers() {
    var db = getDatabase();
    db.transaction(
        function(tx) {
            var rs = tx.executeSql('UPDATE SWM_Task SET Status=? WHERE Status=?;',['中断执行','执行中']);
        }
    );
}

//
function updateStatus(id,seq,status) {
    var db = getDatabase();
    var today=new Date()
    var mon = today.getMonth()+1;
    var nowTime = today.getFullYear()+"-"+mon+"-"+today.getDate()+" "+today.getHours()+":"+today.getMinutes()+":"+today.getSeconds();

    db.transaction(
        function(tx) {
            if(status === "执行中") {
                var rs = tx.executeSql('UPDATE SWM_Task SET Status=?,StartTime=? WHERE TaskID=? AND Sequence=?;',[status,nowTime,id,seq]);
            } else if(status === "已执行") {
                var rs1 = tx.executeSql('UPDATE SWM_Task SET Status=?,EndTime=? WHERE TaskID=? AND Sequence=?;',[status,nowTime,id,seq]);
            } else {
                var rs2 = tx.executeSql('UPDATE SWM_Task SET Status=? WHERE TaskID=? AND Sequence=?;',[status,id,seq]);
            }
        }
    );
}

function getTimeFromID(id) {
    var db = getDatabase();
    var allTime = 0;
    db.transaction(
        function(tx) {
            var cs = tx.executeSql('SELECT * FROM SWM_Task WHERE TaskID=?;',[id]);
            if(cs.rows.length > 0) {
                var length = cs.rows.length;
                for(var i=0;i<length;i++) {
                    allTime += cs.rows.item(i).Time
                }
            }
        }
    );
    return allTime;
}
